Imports MyClasses
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Data.Common

Public Class DbHelper
    Private Shared _connectionString As String

    Public Shared Property ConnectionString() As String
        Get
            Return _connectionString
        End Get

        Set(ByVal value As String)
            _connectionString = value
        End Set
    End Property

    Shared Sub New()
        ConnectionString = Trim(ConfigurationManager.AppSettings("connectionString"))
        'ConnectionString = "server=192.168.0.50;User ID=sa;Password=redhead;Database=" & Trim(ConfigurationManager.AppSettings("DbName")) & ";"
        'ConnectionString = "server=" & Trim(ConfigurationManager.AppSettings("Server")) & ";User ID=" & Trim(ConfigurationManager.AppSettings("UID")) & ";Password=" & Trim(ConfigurationManager.AppSettings("PWD")) & ";Database=" & Trim(ConfigurationManager.AppSettings("DbName")) & ";"
        'ConnectionString = "server=" & Trim(ConfigurationManager.AppSettings("Server")) & ";User ID=" & Trim(ConfigurationManager.AppSettings("UID")) & ";Database=" & Trim(ConfigurationManager.AppSettings("DbName")) & ";"
        'ConnectionString = "Data Source=AVINASH-HP\SQLEXPRESS;Initial Catalog=NGSPaintsDB;Integrated Security=True"
        'ConnectionString = "Data Source=.;Initial Catalog=NGSPaintsDB;Integrated Security=True"
    End Sub

    Public Shared Function GetConnection() As SqlConnection
        Dim Conn As New SqlConnection
        Try
            Conn.ConnectionString = ConnectionString
            Conn.Open()
            Return Conn
        Catch ex As Exception
            Return Conn
        End Try
    End Function

    Public Shared Function ExecuteScalar(ByVal sql As String) As Object
        Dim cnn As New SqlConnection(ConnectionString)
        Dim cmd As New SqlCommand(sql, cnn)
        Try
            cnn.Open()
            Dim retval As Object = cmd.ExecuteScalar
            cnn.Close()
            Return retval
        Finally
            cnn = Nothing
            cmd = Nothing
        End Try
    End Function

    Public Shared Function ExecuteScalar(ByVal sql As String, ByVal params() As SqlParameter) As Object
        Dim cnn As New SqlConnection(ConnectionString)
        Dim cmd As New SqlCommand(sql, cnn)
        Try
            For i As Integer = 0 To params.Length - 1
                cmd.Parameters.Add(params(i))
            Next
            cnn.Open()
            Dim retval As Object = cmd.ExecuteScalar
            cnn.Close()
            Return retval
        Finally
            cnn = Nothing
            cmd = Nothing
        End Try
    End Function


    Public Shared Function ExecuteDataSet(ByVal sql As String) As DataSet
        Dim cnn As New SqlConnection(ConnectionString)
        Dim ds As New DataSet
        Dim cmd As New SqlCommand(sql, cnn)
        Dim da As New SqlDataAdapter(cmd)
        Try
            ds.EnforceConstraints = False
            Try
                cnn.Open()
            Catch ex As Exception

            End Try



            da.Fill(ds)
            cnn.Close()

            Return ds
        Finally
            cnn = Nothing
            cmd = Nothing
            da = Nothing
        End Try
    End Function

    Public Shared Function ExecuteDataSet(ByVal sql As String, ByVal params() As SqlParameter) As DataSet
        Dim ds As New DataSet
        Dim cnn As New SqlConnection(ConnectionString)
        Dim cmd As New SqlCommand(sql, cnn)
        Dim da As New SqlDataAdapter(cmd)
        Try
            For i As Integer = 0 To params.Length - 1
                da.SelectCommand.Parameters.Add(params(i))
            Next
            cnn.Open()
            da.Fill(ds)
            cnn.Close()
            Return ds
        Finally
            cnn = Nothing
            cmd = Nothing
            da = Nothing
        End Try
    End Function

    Public Shared Function ExecuteDataTable(ByVal sql As String) As DataTable
        Dim dt As New DataTable
        Dim cnn As New SqlConnection(ConnectionString)
        Dim cmd As New SqlCommand(sql, cnn)
        Dim da As New SqlDataAdapter(cmd)
        Try
            cnn.Open()
            da.Fill(dt)
            cnn.Close()
            Return dt
        Catch e As Exception


        Finally
            cnn = Nothing
            cmd = Nothing
            da = Nothing
        End Try
    End Function

    Public Shared Function ExecuteDataTable(ByVal sql As String, ByVal params() As SqlParameter) As DataTable
        Dim ds As New DataSet
        Dim cnn As New SqlConnection(ConnectionString)
        Dim cmd As New SqlCommand(sql, cnn)
        Dim da As New SqlDataAdapter(cmd)
        Try
            For i As Integer = 0 To params.Length - 1
                da.SelectCommand.Parameters.Add(params(i))
            Next
            cnn.Open()
            da.Fill(ds)
            cnn.Close()
            Return ds.Tables(0)
        Finally
            cnn = Nothing
            cmd = Nothing
            da = Nothing
        End Try
    End Function

    Public Shared Function ExecuteDataSetSP(ByVal spname As String, ByVal params() As SqlParameter) As DataSet
        Dim cnn As New SqlConnection(ConnectionString)
        Dim ds As New DataSet
        Dim cmd As New SqlCommand(spname, cnn)
        cmd.CommandType = CommandType.StoredProcedure
        Dim da = New SqlDataAdapter(cmd)
        Try
            For i As Integer = 0 To params.Length - 1
                cmd.Parameters.Add(params(i))
            Next
            cnn.Open()

            da.Fill(ds)
            cnn.Close()

            Return ds
        Finally
            cnn = Nothing
            cmd = Nothing
            da = Nothing
        End Try
    End Function

    Public Shared Function ExecuteSP(ByVal spname As String, ByVal params() As SqlParameter) As Integer

        Dim cnn As New SqlConnection(ConnectionString)
        Dim cmd As New SqlCommand(spname, cnn)
        cmd.CommandType = CommandType.StoredProcedure
        Try
            For i As Integer = 0 To params.Length - 1
                cmd.Parameters.Add(params(i))
            Next

            cnn.Open()
            Dim retval As Integer = cmd.ExecuteNonQuery()
            cnn.Close()

            Return retval
        Finally
            cnn = Nothing
            cmd = Nothing
        End Try
    End Function

    Public Shared Function ExecuteNonQuery(ByVal sql As String) As Integer
        Dim cnn As New SqlConnection(ConnectionString)
        Dim cmd As New SqlCommand(sql, cnn)
        Try
            cnn.Open()
            Dim retval As Integer = cmd.ExecuteNonQuery()
            cnn.Close()
            Return retval
        Finally
            cnn = Nothing
            cmd = Nothing
        End Try
    End Function

    Public Shared Function ExecuteNonQuery(ByVal sql As String, ByVal params() As SqlParameter) As Integer
        Dim cnn As New SqlConnection(ConnectionString)
        Dim cmd As New SqlCommand(sql, cnn)
        Try
            For i As Integer = 0 To params.Length - 1
                cmd.Parameters.Add(params(i))
            Next
            cnn.Open()
            Dim retval As Integer = cmd.ExecuteNonQuery()
            cnn.Close()
            Return retval
        Catch ex As Exception

        Finally
            cnn = Nothing
            cmd = Nothing
        End Try
    End Function

    Public Shared Function ExecuteDataReader(ByVal sql As String, ByVal params() As SqlParameter)
        Dim myReader As SqlDataReader
        Dim cnn As New SqlConnection(ConnectionString)
        Dim cmd As New SqlCommand("ProductList", cnn)
        cmd.CommandType = CommandType.StoredProcedure

        Try
            For i As Integer = 0 To params.Length - 1
                cmd.Parameters.Add(params(i))
            Next
            cnn.Open()
            myReader = cmd.ExecuteReader()
            Return myReader
            ' cnn.Close()
        Finally
            ' cnn = Nothing
            '  cmd = Nothing
        End Try
    End Function

    Public Shared Function ExecuteDataReader(ByVal sql As String) As SqlDataReader
        Dim myReader As SqlDataReader
        Dim cnn As New SqlConnection(ConnectionString)
        Dim cmd As New SqlCommand(sql, cnn)

        Try

            cnn.Open()
            myReader = cmd.ExecuteReader()
            Return myReader
            ' cnn.Close()
        Finally
            ' cnn = Nothing
            '  cmd = Nothing
        End Try
    End Function





    Public Shared Sub ExecuteDataReader(ByVal sql As String, ByRef myReader As SqlDataReader)

        Dim cnn As New SqlConnection(ConnectionString)
        Dim cmd As New SqlCommand(sql, cnn)

        Try

            cnn.Open()
            myReader = cmd.ExecuteReader() 'CommandBehavior.CloseConnection)
            'cnn.Close()
        Finally
            ' cnn = Nothing
            '  cmd = Nothing
        End Try
    End Sub
End Class
